# -*- coding: utf-8 -*-
# @Time    : 2024/9/13 11:03
# @File    : a4.py
# @Action  : ..
import time
import os
import sys
import dictdiffer
import sqlite3
import datetime
import pandas as pd
from flask import Flask, jsonify, request, render_template
from flask_cors import CORS
from flask_sqlalchemy import SQLAlchemy


# current_dir = os.path.dirname(os.path.abspath(sys.argv[0]))
current_dir = os.getcwd().replace('\\', '/')
print('当前目录', current_dir)

app = Flask(__name__, static_folder=current_dir + '/static',
            template_folder=current_dir + '/templates')
app.config["JSON_AS_ASCII"] = False
CORS(app, supports_credentials=True)

# os.path.join(current_dir, '')
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///' + os.path.join(current_dir, 'database.db')
# 该字段增加了大量的开销，会被禁用，建议设置为False
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

fields = ['id', '区域属名', '所在部门', '设备名称', '设备照片', '所属网络安全分区', '运行状态', '三区管理地址1',
          '三区管理地址2',
          '四区管理地址1', '四区管理地址2', '五区管理地址', '放置地点', '房间', '屏柜', 'U数', '登录方式', '是否启用3A',
          '用户名', '密码', 'enable密码', '厂商', '类型', '型号', '序列号', '软件版本', '设备名', '备注',
          'created_time']
fields_one = fields.copy()
where_cont = ['`%s`' % i for i in fields[1:-1]]
where_cont = ','.join(where_cont)
fields = [[i, i] for i in fields]
fields[-1][1] = '创建时间'


class Switch(db.Model):
    # 设置表名，如果不设置则为小写的类名。
    __tablename__ = 'switch'
    id = db.Column(db.Integer, primary_key=True)
    区域属名 = db.Column(db.Text)
    所在部门 = db.Column(db.Text)
    设备名称 = db.Column(db.Text)
    设备照片 = db.Column(db.Text)
    所属网络安全分区 = db.Column(db.Text)
    运行状态 = db.Column(db.Text)
    三区管理地址1 = db.Column(db.Text)
    三区管理地址2 = db.Column(db.Text)
    四区管理地址1 = db.Column(db.Text)
    四区管理地址2 = db.Column(db.Text)
    五区管理地址 = db.Column(db.Text)
    放置地点 = db.Column(db.Text)
    房间 = db.Column(db.Text)
    屏柜 = db.Column(db.Text)
    U数 = db.Column(db.Text)
    登录方式 = db.Column(db.Text)
    是否启用3A = db.Column(db.Text)
    用户名 = db.Column(db.Text)
    密码 = db.Column(db.Text)
    enable密码 = db.Column(db.Text)
    厂商 = db.Column(db.Text)
    类型 = db.Column(db.Text)
    型号 = db.Column(db.Text)
    序列号 = db.Column(db.Text)
    软件版本 = db.Column(db.Text)
    设备名 = db.Column(db.Text)
    备注 = db.Column(db.Text)
    # 加固时间 = db.Column(db.Text)
    # 加固人员 = db.Column(db.Text)
    # 加固情况 = db.Column(db.Text)
    created_time = db.Column(db.Text)

    def to_dict(self):
        return {c.name: getattr(self, c.name) for c in self.__table__.columns}

    def __repr__(self):
        return '<Switch %r>' % self.设备名称


@app.route('/')
def index():
    return render_template('switch.html')


@app.route('/update', methods=['POST', 'GET'])
def update():
    from_data = request.form.to_dict()
    print('调用了修改', from_data)
    k_list = list(from_data.keys())
    for k1 in k_list:
        if k1 not in fields_one:
            print('删除多余的字段：', k1)
            from_data.pop(k1)
    obj = Switch.query.filter_by(id=int(from_data.pop('id')))
    obj_data = obj.first().to_dict()
    obj_data.pop("id")
    obj_data.pop("created_time")
    for k in obj_data.keys():
        if obj_data[k] is None:
            obj_data[k] = ""
    print('obj_data==', obj_data)
    # 检查变化的键和值
    change = list(dictdiffer.diff(obj_data, from_data))
    up_dict = {i[1]: i[-1][-1] for i in change}
    print('change', change)
    print(up_dict)
    obj.update(up_dict)
    db.session.commit()
    result = {"code": 0, "msg": "ok"}
    return jsonify(result)


@app.route('/delete', methods=['POST', 'GET'])
def delete():
    from_data = request.form.to_dict()
    print('调用了删除', from_data)
    Switch.query.filter(Switch.id == int(from_data['id'])).delete()
    db.session.commit()
    result = {"code": 0, "msg": "ok"}
    return jsonify(result)


@app.route('/add', methods=['POST', 'GET'])
def add_info():
    from_data = request.form.to_dict()
    print('调用了增加:', from_data)
    k_list = list(from_data.keys())
    for k1 in k_list:
        if k1 not in fields_one:
            print('删除多余的字段2：', k1)
            from_data.pop(k1)
    from_data.update({'created_time': datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')})
    db.session.add(Switch(**from_data))
    db.session.commit()
    result = {"code": 0, "msg": "ok"}
    return jsonify(result)


@app.route("/upload", methods=["POST"])
def upload():
    file = request.files['file']
    file_name = file.filename
    print('请求', request)
    print('file:', file)
    sources = 'img'
    if '.xl' in file_name:
        sources = 'file'
    # else:
    #     ts = time.strftime('%Y%m%d%H%M%S', time.localtime())
    #     file_name = file_name.replace('.', '_' + ts + '.')
    print('file_name', file_name)
    # file_path = os.path.join(current_dir, 'static/img', file.filename)
    save_path = f'./static/{sources}/' + file_name  # python 用的路径
    file.save(save_path)  # 保存文件
    src_path = f"../static/{sources}/" + file_name  # 前端用的路径
    if sources == 'file':
        with open(f'./static/{sources}/' + 'data.txt', 'w', encoding='utf-8') as fp:
            fp.write(save_path)
    result = {"code": 0, "msg": "success", "data": {'src': src_path}, 'filename': file_name}
    return jsonify(result)


@app.route("/import", methods=["POST", "GET"])
def import_data():
    with open(f'./static/file/' + 'data.txt', 'r', encoding='utf-8') as fp:
        file_path = fp.read()
    print(file_path)
    print(request.form.to_dict())
    df = pd.read_excel(file_path, dtype=object)
    df['设备照片'] = '[{"src":"../static/img/null.png"}]'
    conn = sqlite3.connect('database.db')
    df.to_sql('switch', conn, if_exists='append', index=False)
    conn.close()
    result = {"code": 0, "msg": "导入成功", 'count': df.shape[0]}
    return jsonify(result)


@app.route('/switch', methods=['POST', 'GET'])
def switch_list():
    print(request)
    count = 0
    data_list = []
    from_data = request.form.to_dict()
    print(from_data)
    from_data = {k: v for k, v in from_data.items() if v != ''}
    print(from_data)
    # conn = sqlite3.connect('database.db')
    # df = pd.read_sql(sql='SELECT * FROM switch LIMIT 10', con=conn)
    # conn.close()
    # print('df', df)
    # for ind, row in df.iterrows():
    #     data_list.append(row.to_dict())
    # cols = [[c, c] for c in df.columns.tolist()]
    # cols[-1][1] = '创建时间'
    # print(cols)
    # data = {
    #     "code": 0, "msg": "", "count": df.shape[0], "data": data_list, 'cols': cols,
    # }
    if request.method == 'POST':
        limit = int(from_data['limit'])
        offset = (int(from_data['page']) - 1) * limit
    else:
        limit = 10
        offset = 0
    print('offset', offset, 'limit', limit)
    where_list = []
    # 查询全部数据，没有where条件
    if len(from_data) <= 2:
        count = Switch.query.count()
        if limit == 10000:
            print('本次为导入全部数据，不加limit-0')
            objs = Switch.query.all()
        else:
            objs = Switch.query.offset(offset).limit(limit).all()
        for obj in objs:
            data_list.append(obj.to_dict())
    else:
        # 查询有where条件筛选
        keys = list(from_data.keys())[2:]
        if 'keyWord' in keys:
            keys = ['keyWord']
        for key in keys:
            val = from_data[key]
            if key == 'IP地址':
                cols = ['三区管理地址1', '三区管理地址2', '四区管理地址1', '四区管理地址2']
                wh = ' OR '.join([f"`{i}`='{val}'" for i in cols])
                print('wh1', wh)
            elif key == 'keyWord':
                wh = ' OR '.join([f"`{i}` LIKE '%{val}%'" for i in fields_one[1:-1]])
                print('wh2', wh)
            else:
                wh = f'`{key}` LIKE "%{val}%"'
            where_list.append(wh)
        wheres = ' AND '.join(where_list)
        # 通过sql语句查询数据
        conn = sqlite3.connect('database.db')
        sql_fill = 'SELECT * FROM switch WHERE ' + wheres
        sql_count = sql_fill.replace('*', 'COUNT(*)')
        if limit == 10000:
            print('本次为导入全部数据，不加limit')
        else:
            sql_fill = sql_fill + f' LIMIT {offset}, {limit}'
        print('sql_fill', sql_fill)
        # 获取总数量
        df0 = pd.read_sql(sql=sql_count, con=conn)
        count = int(df0.values[0][0])
        # 查找指定数量的数据
        df = pd.read_sql(sql=sql_fill, con=conn)
        conn.close()
        # print('df', df)
        for ind, row in df.iterrows():
            data_list.append(row.to_dict())
    data = {
        "code": 0, "msg": "", "count": count, "data": data_list, 'cols': fields,
    }
    result = jsonify(data)
    return result


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5001, debug=True)
